//This class generates 200 employees and inserts the data into local javabootcamp database, employee table.
//It also creates 200 insert statements, one for each employee and enters them into a text file as a backup.	

//Run as Java Application//

//*******************************************************************************************************
package ie.deloitte.cvapp.generateImportData;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Random;

public class GenerateRun {
	// Initialise Variables
	static int i = 1;
	static int id = 0;
	static String name;
	static String surname;
	static String profile;
	static String role;
	static String roleproposed;
	static String email;
	static String photo;
	static String date;
	static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	static String primarytelephone;
	static String secondarytelepohne;
	static Random randomGenerator = new Random();

	// Main Program
	public static void main(String[] args) throws IOException, SQLException,
			ClassNotFoundException {

		// Create File and methods to write to file
		File file = new File("Testdata.txt");
		FileWriter fileWriter = new FileWriter(file);
		PrintWriter printWriter = new PrintWriter(fileWriter);

		// load the JDBC driver
		Class.forName("com.mysql.jdbc.Driver");

		// get a connection to the database
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/javabootcamp", "root", "admin");

		// empty table
//		Statement stmt = con.createStatement();
//		stmt.execute("truncate employee;");
//		System.out.println("employee table emptied.");

		// Prepare and create query
		PreparedStatement pstmt = null;
		String query = "INSERT INTO "
				+ "employee(creationdate,email,name,photo,primarytelephone,profile,roleproposed,secondarytelepohne,surname)"
				+ "VALUES( 		?, 			?, 		?, 		?, 			?, 				?	, 		?, 				?, 				?)";
		pstmt = con.prepareStatement(query);

		int count = 0;
		for (i = 1; i < 200; i++) {

			// Generate values for variables
			id = i;
			name = FIRST_NAMES[i % 10];
			surname = LAST_NAMES[(int) Math.floor(i / 20)];
			profile = "This is " + name + " " + surname + "'s  biography";
			roleproposed = randomRole().toString();
			StringBuilder datea = new StringBuilder(
					dateFormat.format(createDate(i)));
			date = datea.toString();
			photo = name + surname + ".jpg";
			email = name + surname + "@deloitte.ie";
			primarytelephone = "123-1234567";
			secondarytelepohne = "123-1234567";

			// Input variables into query
			pstmt.clearParameters();
			pstmt.setString(1, date);
			pstmt.setString(2, email);
			pstmt.setString(3, name);
			pstmt.setString(4, photo);
			pstmt.setString(5, primarytelephone);
			pstmt.setString(6, profile);
			pstmt.setString(7, roleproposed);
			pstmt.setString(8, secondarytelepohne);
			pstmt.setString(9, surname);

			// execute insert statement
			pstmt.executeUpdate();

			// write Insert statements to text file
			printWriter.write("Insert into employee values('" + id + ","
					+ datea + "," + name + "," + photo + ", " + profile + ","
					+ roleproposed + "," + email + "," + primarytelephone + ","
					+ secondarytelepohne + "," + surname + "');");
			printWriter.write("\n");
			count++;
		}
		System.out.println(count + " rows entered into the employee table");
	}

	// Array of First names
	final static String[] FIRST_NAMES = { "John", "Paul", "George", "Ringo",
			"Joanne", "Paula", "Georgina", "Rebecca", "Dirk", "Stig", "Nasty",
			"Barry", "Diane", "Stacy", "Natasha", "Barbara", "Laurence", };

	// Array of Surnames
	final static String[] LAST_NAMES = { "McGough", "Blair", "Brown",
			"Donoghue", "McGovern", "Smith", "Jones", "Clarke", "O'Driscoll",
			"Forsyth", "Greer", };

	// Generate a date based on iteration i
	public static java.util.Date createDate(int i) {
		Calendar cal = Calendar.getInstance();
		cal.clear();
		cal.set(Calendar.YEAR, 2012);
		cal.set(Calendar.MONTH, i % 12);
		cal.set(Calendar.DAY_OF_MONTH, i % 28 + 1);
		return cal.getTime();
	}

	
	//Randomly generates a role for each person and is weighted so that there are 
	//less people at management level than below management level
	public static String randomRole() {
		switch (randomGenerator.nextInt(13)) {
		case 0:
			role = "Analyst";
			break;
		case 1:
			role = "Consultant";
			break;
		case 2:
			role = "Senior Consultant";
			break;
		case 3:
			role = "Manager";
			break;
		case 4:
			role = "Senior Manager";
			break;
		case 5:
			role = "Director";
			break;
		case 6:
			role = "Partner";
			break;
		case 7:
			role = "Manager";
			break;
		case 8:
			role = "Analyst";
			break;
		case 9:
			role = "Analyst";
			break;
		case 10:
			role = "Consultant";
			break;
		case 11:
			role = "Consultant";
			break;
		case 12:
			role = "Senior Consultant";
			break;
		}
		return role;
	}
}
